﻿using Microsoft.VisualStudio.TestTools.UnitTesting;
using MongoDB.Driver;
using MongoDB.Driver.Builders;
using MongoDB.Driver.Linq;
using Sustainalytics.ClientDW.Entities.Universes;
using Sustainalytics.Utils.Exceptions.Universes;
using Sustainalytics.Auth.Claims;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;


namespace Sustainalytics.Universes.Service.Tests
{
    [TestClass]
    public class UniversesRepositoryTests
    {
        private string dbCnx = "mongodb://localhost:27017/ClientUniverses_Tests";
        private string databaseName = "ClientUniverses_Tests";
        private string collectionName = typeof(UploadLog).Name;

        private MongoDatabase database = null;


        [TestInitialize]
        public void Initialize()
        {
            var mongoClient = new MongoClient(dbCnx);
            var server = mongoClient.GetServer();
            database = server.GetDatabase(databaseName);
        }

        [TestCleanup]
        public void Cleanup()
        {
            database.GridFS.Files.RemoveAll();
            database.Drop();
        }

        [TestMethod]
        [Owner("Mihai Stanescu")]
        public void UploadFile()
        {
            var universeRep = Sustainalytics.ClientDW.DataAccessLayer.UniversesRepository.GetUniversesRepository(dbCnx);
            var result = universeRep.UploadFile(Guid.NewGuid(), "newfile", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            var buffer = File.ReadAllBytes(@"./Resources/universe_upload.xlsx");

            var i = (int)0;
            for (; i + 1024 < buffer.Length; i += 1024)
                result.Item1.Write(buffer, i, 1024);
            result.Item1.Write(buffer, i, buffer.Length - i);
            result.Item1.Close();


            var inserted = new MemoryStream(buffer);
            var foundFi = database.GridFS.FindOne(Query.EQ("_id", result.Item2));

            Assert.AreEqual(foundFi.Name, "newfile", "File name does not match");
            Assert.AreEqual(foundFi.ContentType, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Content type does not match");
            Assert.IsTrue(UniversesControllerTests.StreamsAreIdentical(inserted, foundFi.OpenRead()));
        }

        [TestMethod]
        [Owner("Mihai Stanescu")]
        public void IsProcessingScheduled()
        {
            var universeRep = Sustainalytics.ClientDW.DataAccessLayer.UniversesRepository.GetUniversesRepository(dbCnx);
            UploadLog log = new UploadLog()
            {
                Created = DateTime.UtcNow,
                FileId = Guid.NewGuid(),
                UserId = Guid.NewGuid(),
                UploadType = UploadType.UniversesUpload,
                Processed = true,
                Id = Guid.NewGuid()
            };
            
            universeRep.AddUploadLogs(log);
            var coll = database.GetCollection<UploadLog>(typeof(UploadLog).Name).AsQueryable();
            var savedLog = coll.OrderByDescending(l=> l.Created).FirstOrDefault(l => l.Created > DateTime.Today);

            Assert.IsNotNull(savedLog, "The upload log cannot be found.");
            Assert.IsTrue(savedLog.Processed, "The value of processes is different than the expected one.");
        }

        [TestMethod]
        [Owner("Mihai Stanescu")]
        public void AddUploadLogs()
        {
            var universeRep = Sustainalytics.ClientDW.DataAccessLayer.UniversesRepository.GetUniversesRepository(dbCnx);
            UploadLog log = new UploadLog()
            {
                Created = DateTime.UtcNow,
                FileId = Guid.NewGuid(),
                UserId = Guid.NewGuid(),
                UploadType = UploadType.UniversesUpload,
                Processed = true,
                Id = Guid.NewGuid()
            };

            universeRep.AddUploadLogs(log);
            var savedLog = universeRep.GetUploadLog();

            Assert.IsNotNull(savedLog, "The upload log cannot be found.");
            Assert.IsTrue(savedLog.Processed, "The value of processes is different than the expected one.");
        }

        [TestMethod]
        [Owner("Mihai Stanescu")]
        [ExpectedException(typeof(DuplicateUploadLogsException))]
        public void AddDuplicateUploadLogs()
        {
            var universeRep = Sustainalytics.ClientDW.DataAccessLayer.UniversesRepository.GetUniversesRepository(dbCnx);
            UploadLog log = new UploadLog()
            {
                Id = Guid.NewGuid(),
                Created = DateTime.UtcNow,
                FileId = Guid.NewGuid(),
                UserId = Guid.NewGuid(),
                UploadType = UploadType.UniversesUpload,
                Processed = true
            };

            universeRep.AddUploadLogs(log);
            universeRep.AddUploadLogs(log);
        }

        [TestMethod]
        [Owner("Mihai Stanescu")]
        public void AddFeatureUniverse()
        {
            var universeRep = Sustainalytics.ClientDW.DataAccessLayer.UniversesRepository.GetUniversesRepository(dbCnx);
            FeatureUniverse expectedFeatureUniverse = new FeatureUniverse()
            {
                AccountId = Guid.NewGuid(),
                Feature = AccountFeature.CompanySearch,
                UniverseIds = new List<int>() { 1, 4, 6 }
            };

            universeRep.AddFeatureUniverse(expectedFeatureUniverse);
            var featureUniverse = universeRep.GetFeatureUniverse(expectedFeatureUniverse.AccountId, expectedFeatureUniverse.Feature);

            Assert.IsNotNull(featureUniverse, "The returned feature universe should not be null.");
            Assert.AreEqual(expectedFeatureUniverse.AccountId, featureUniverse.AccountId, "The accountId is different than the expected one.");
            Assert.AreEqual(expectedFeatureUniverse.Feature, featureUniverse.Feature, "The feature is different than the expected one.");
            Assert.AreEqual(expectedFeatureUniverse.UniverseIds.Count, featureUniverse.UniverseIds.Count, "The number of universes is different than the expected one.");
        }

        [TestMethod]
        [Owner("Mihai Stanescu")]
        [ExpectedException(typeof(DuplicateFeatureUniverseException))]
        public void AddDuplicateFeatureUniverse()
        {
            var universeRep = Sustainalytics.ClientDW.DataAccessLayer.UniversesRepository.GetUniversesRepository(dbCnx);
            FeatureUniverse expectedFeatureUniverse = new FeatureUniverse()
            {
                AccountId = Guid.NewGuid(),
                Feature = AccountFeature.CompanySearch,
                UniverseIds = new List<int>() { 1, 4, 6 }
            };

            universeRep.AddFeatureUniverse(expectedFeatureUniverse);
            universeRep.AddFeatureUniverse(expectedFeatureUniverse);
        }
   
        [TestMethod]
        [Owner("Mihai Stanescu")]
        public void AddUniverse()
        {
            var universeRep = Sustainalytics.ClientDW.DataAccessLayer.UniversesRepository.GetUniversesRepository(dbCnx);
            Universe expectedUniverse = new Universe()
            {
                Id = 1,
                AccountId = Guid.NewGuid(),
                Created = DateTime.UtcNow,
                LastUpdated = DateTime.UtcNow,
                Name = "SomeUniverseName"
            };

            universeRep.AddUniverse(expectedUniverse);
            var universe = universeRep.GetUniverse("SomeUniverseName", expectedUniverse.AccountId);

            Assert.IsNotNull(universe, "The returned universe should not be null.");
            Assert.AreEqual(expectedUniverse.Id, universe.Id, "The id of the universe is different than the expected one.");
            Assert.AreEqual(expectedUniverse.AccountId, universe.AccountId, "The accountId is different than tht expected one.");
            Assert.AreEqual(expectedUniverse.Name, universe.Name, "The name of the universe is different than the expected one.");
        }

        [TestMethod]
        [Owner("Mihai Stanescu")]
        [ExpectedException(typeof(DuplicateUniverseException))]
        public void DuplicateAddUniverse()
        {
            var universeRep = Sustainalytics.ClientDW.DataAccessLayer.UniversesRepository.GetUniversesRepository(dbCnx);
            Universe expectedUniverse = new Universe()
            {
                Id = 1,
                AccountId = Guid.NewGuid(),
                Created = DateTime.UtcNow,
                LastUpdated = DateTime.UtcNow,
                Name = "SomeUniverseName"
            };

            universeRep.AddUniverse(expectedUniverse);
            universeRep.AddUniverse(expectedUniverse);
        }

        [TestMethod]
        [Owner("Mihai Stanescu")]
        public void AddUniverseCompany()
        {
            var universeRep = Sustainalytics.ClientDW.DataAccessLayer.UniversesRepository.GetUniversesRepository(dbCnx);
            UniverseCompany expectedUniverse = new UniverseCompany()
            {
                CIQID = "ID1239853",
                CompanyId = Guid.NewGuid(),
                UniverseId = 4,
                LastUpdated = DateTime.UtcNow
            };

            universeRep.AddUniverseCompany(expectedUniverse);
            var universeCompany = universeRep.GetUniverseCompany(expectedUniverse.CompanyId, expectedUniverse.UniverseId);

            Assert.IsNotNull(universeCompany, "The universe company should not be null.");
            Assert.AreEqual(expectedUniverse.CIQID, universeCompany.CIQID, "The CIQID is different than the expected one.");
            Assert.AreEqual(expectedUniverse.UniverseId, universeCompany.UniverseId, "The universeId is different than the expected one.");
        }

        [TestMethod]
        [Owner("Mihai Stanescu")]
        [ExpectedException(typeof(DuplicateUniverseCompanyException))]
        public void DuplicateAddUniverseCompany()
        {
            var universeRep = Sustainalytics.ClientDW.DataAccessLayer.UniversesRepository.GetUniversesRepository(dbCnx);
            UniverseCompany expectedUniverse = new UniverseCompany()
            {
                CIQID = "ID1239853",
                CompanyId = Guid.NewGuid(),
                UniverseId = 4,
                LastUpdated = DateTime.UtcNow
            };

            universeRep.AddUniverseCompany(expectedUniverse);
            universeRep.AddUniverseCompany(expectedUniverse);
        }

        [TestMethod]
        [Owner("Mihai Stanescu")]
        public void DeleteAllUniverseCompanies()
        {
            var universeRep = Sustainalytics.ClientDW.DataAccessLayer.UniversesRepository.GetUniversesRepository(dbCnx);
            UniverseCompany expectedUniverse1 = new UniverseCompany()
            {
                CIQID = "ID1279853",
                CompanyId = Guid.NewGuid(),
                UniverseId = 4,
                LastUpdated = DateTime.UtcNow
            };
      
            universeRep.AddUniverseCompany(expectedUniverse1);
            universeRep.DeleteAllUniverseCompanies();
            var universeCompany1 = universeRep.GetUniverseCompany(expectedUniverse1.CompanyId, expectedUniverse1.UniverseId);

            Assert.IsNull(universeCompany1, "The universe company should be null.");
        }

        [TestMethod]
        [Owner("Mihai Stanescu")]
        public void DeleteAllUniverses()
        {
            var universeRep = Sustainalytics.ClientDW.DataAccessLayer.UniversesRepository.GetUniversesRepository(dbCnx);
            Universe expectedUniverse = new Universe()
            {
                Id = 1,
                AccountId = Guid.NewGuid(),
                Created = DateTime.UtcNow,
                LastUpdated = DateTime.UtcNow,
                Name = "SomeUniverseName"
            };

            universeRep.AddUniverse(expectedUniverse);
            universeRep.DeleteAllUniverses();
            var universe = universeRep.GetUniverse("SomeUniverseName", expectedUniverse.AccountId);

            Assert.IsNull(universe, "The universe should be null.");
        }

        [TestMethod]
        [Owner("Mihai Stanescu")]
        public void DeleteAllFeatureUniverses()
        {
            var universeRep = Sustainalytics.ClientDW.DataAccessLayer.UniversesRepository.GetUniversesRepository(dbCnx);
            FeatureUniverse featureUniverse = new FeatureUniverse()
            {
                AccountId = Guid.NewGuid(),
                Feature = AccountFeature.GlobalCompactNonCompliant,
                UniverseIds = new List<int>() { 1,5,7}
            };

            universeRep.AddFeatureUniverse(featureUniverse);
            universeRep.DeleteAllFeatureUniverses();
            var universe = universeRep.GetFeatureUniverse(featureUniverse.AccountId, AccountFeature.GlobalCompactNonCompliant);

            Assert.IsNull(universe, "The universe should be null.");
        }

        [TestMethod]
        [Owner("Mihai Stanescu")]
        public void DeleteUniverse()
        {
            var universeRep = Sustainalytics.ClientDW.DataAccessLayer.UniversesRepository.GetUniversesRepository(dbCnx);
            Universe expectedUniverse = new Universe()
            {
                Id = 1,
                AccountId = Guid.NewGuid(),
                Created = DateTime.UtcNow,
                LastUpdated = DateTime.UtcNow,
                Name = "SomeUniverseName"
            };
            Universe expectedUniverse2 = new Universe()
            {
                Id = 2,
                AccountId = Guid.NewGuid(),
                Created = DateTime.UtcNow,
                LastUpdated = DateTime.UtcNow,
                Name = "SomeUniverseName2"
            };

            universeRep.AddUniverse(expectedUniverse);
            universeRep.AddUniverse(expectedUniverse2);
            universeRep.DeleteUniverse(2);
            var universe = universeRep.GetUniverse("SomeUniverseName", expectedUniverse.AccountId);
            var universe2 = universeRep.GetUniverse("SomeUniverseName2", expectedUniverse2.AccountId);

            Assert.IsNull(universe2, "The universe should be null.");
            Assert.IsNotNull(universe, "The returned universe should not be null.");
            Assert.AreEqual(expectedUniverse.Id, universe.Id, "The id of the universe is different than the expected one.");
            Assert.AreEqual(expectedUniverse.AccountId, universe.AccountId, "The accountId is different than tht expected one.");
            Assert.AreEqual(expectedUniverse.Name, universe.Name, "The name of the universe is different than the expected one.");
        }

        [TestMethod]
        [Owner("Mihai Stanescu")]
        public void GetFeatureUniverse()
        {
            var universeRep = Sustainalytics.ClientDW.DataAccessLayer.UniversesRepository.GetUniversesRepository(dbCnx);
            FeatureUniverse expectedFeatureUniverse = new FeatureUniverse()
            {
                AccountId = Guid.NewGuid(),
                Feature = AccountFeature.CompanySearch,
                UniverseIds = new List<int>() { 1, 4, 6 }
            };

            universeRep.AddFeatureUniverse(expectedFeatureUniverse);
            var featureUniverse = universeRep.GetFeatureUniverse(expectedFeatureUniverse.AccountId, expectedFeatureUniverse.Feature);

            Assert.IsNotNull(featureUniverse, "The returned feature universe should not be null.");
            Assert.AreEqual(expectedFeatureUniverse.AccountId, featureUniverse.AccountId, "The accountId is different than the expected one.");
            Assert.AreEqual(expectedFeatureUniverse.Feature, featureUniverse.Feature, "The feature is different than the expected one.");
            Assert.AreEqual(expectedFeatureUniverse.UniverseIds.Count, featureUniverse.UniverseIds.Count, "The number of universes is different than the expected one.");
        }

        [TestMethod]
        [Owner("Mihai Stanescu")]
        public void GetFile()
        {
            var universeRep = Sustainalytics.ClientDW.DataAccessLayer.UniversesRepository.GetUniversesRepository(dbCnx);
            var result = universeRep.UploadFile(Guid.NewGuid(), "newfile", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            var buffer = File.ReadAllBytes(@"./Resources/universe_upload.xlsx");

            var i = (int)0;
            for (; i + 1024 < buffer.Length; i += 1024)
                result.Item1.Write(buffer, i, 1024);
            result.Item1.Write(buffer, i, buffer.Length - i);
            result.Item1.Close();


            var inserted = new MemoryStream(buffer);
            var foundFi = database.GridFS.FindOne(Query.EQ("_id", result.Item2));

            Assert.AreEqual(foundFi.Name, "newfile", "File name does not match");
            Assert.AreEqual(foundFi.ContentType, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Content type does not match");
            Assert.IsTrue(UniversesControllerTests.StreamsAreIdentical(inserted, foundFi.OpenRead()));
        }

        [TestMethod]
        [Owner("Mihai Stanescu")]
        public void GetUniverse()
        {
            var universeRep = Sustainalytics.ClientDW.DataAccessLayer.UniversesRepository.GetUniversesRepository(dbCnx);
            Universe expectedUniverse = new Universe()
            {
                Id = 1,
                AccountId = Guid.NewGuid(),
                Created = DateTime.UtcNow,
                LastUpdated = DateTime.UtcNow,
                Name = "SomeUniverseName"
            };

            universeRep.AddUniverse(expectedUniverse);
            var universe = universeRep.GetUniverse("SomeUniverseName", expectedUniverse.AccountId);

            Assert.IsNotNull(universe, "The returned universe should not be null.");
            Assert.AreEqual(expectedUniverse.Id, universe.Id, "The id of the universe is different than the expected one.");
            Assert.AreEqual(expectedUniverse.AccountId, universe.AccountId, "The accountId is different than tht expected one.");
            Assert.AreEqual(expectedUniverse.Name, universe.Name, "The name of the universe is different than the expected one.");
        }

        [TestMethod]
        [Owner("Mihai Stanescu")]
        public void GetUniversesByAccountId()
        {
            var universeRep = Sustainalytics.ClientDW.DataAccessLayer.UniversesRepository.GetUniversesRepository(dbCnx);
            Guid accountId = Guid.NewGuid();

            Universe universe = new Universe()
            {
                Id = 1,
                AccountId = accountId,
                Created = DateTime.UtcNow,
                LastUpdated = DateTime.UtcNow,
                Name = "SomeUniverseName"
            };
            Universe universe2 = new Universe()
            {
                Id = 2,
                AccountId = accountId,
                Created = DateTime.UtcNow,
                LastUpdated = DateTime.UtcNow,
                Name = "SomeUniverseName2"
            };

            universeRep.AddUniverse(universe);
            universeRep.AddUniverse(universe2);
            var universes = universeRep.GetUniversesByAccountId(accountId);

            Assert.IsNotNull(universes, "The returned universes list should not be null.");
            Assert.AreEqual(2, universes.Count, "The number of universes is different than the expected one.");
            Assert.IsNotNull(universes.FirstOrDefault(u=> u.Id == 2), "The universe with id 2 was not found.");
        }

        [TestMethod]
        [Owner("Mihai Stanescu")]
        public void UpdateFeatureUniverses()
        {
            var universeRep = Sustainalytics.ClientDW.DataAccessLayer.UniversesRepository.GetUniversesRepository(dbCnx);
            var updatedUniversesList = new List<int>() { 3, 7, 1, 9 };
            FeatureUniverse expectedFeatureUniverse = new FeatureUniverse()
            {
                AccountId = Guid.NewGuid(),
                Feature = AccountFeature.CompanySearch,
                UniverseIds = new List<int>() { 1, 4, 6 }
            };

            universeRep.AddFeatureUniverse(expectedFeatureUniverse);
            universeRep.UpdateFeatureUniverses(expectedFeatureUniverse.AccountId, expectedFeatureUniverse.Feature, updatedUniversesList);

            var featureUniverse = universeRep.GetFeatureUniverse(expectedFeatureUniverse.AccountId, expectedFeatureUniverse.Feature);

            Assert.IsNotNull(featureUniverse, "The returned feature universe should not be null.");
            Assert.AreEqual(expectedFeatureUniverse.AccountId, featureUniverse.AccountId, "The accountId is different than the expected one.");
            Assert.AreEqual(expectedFeatureUniverse.Feature, featureUniverse.Feature, "The feature is different than the expected one.");
            Assert.AreEqual(updatedUniversesList.Count, featureUniverse.UniverseIds.Count, "The number of universes is different than the expected one.");
            Assert.IsTrue(featureUniverse.UniverseIds.ContainsAll(updatedUniversesList), "The universes list is different than the expected one.");
        }

        [TestMethod]
        [Owner("Mihai Stanescu")]
        public void GetUploadLog()
        {
            var universeRep = Sustainalytics.ClientDW.DataAccessLayer.UniversesRepository.GetUniversesRepository(dbCnx);
            UploadLog log = new UploadLog()
            {
                Created = DateTime.UtcNow,
                FileId = Guid.NewGuid(),
                UserId = Guid.NewGuid(),
                UploadType = UploadType.UniversesUpload,
                Processed = true,
                Id = Guid.NewGuid()
            };

            universeRep.AddUploadLogs(log);
            var savedLog = universeRep.GetUploadLog();

            //var coll = database.GetCollection<UploadLog>(typeof(UploadLog).Name).AsQueryable();
            //var savedLog = coll.OrderByDescending(l => l.Created).FirstOrDefault(l => l.Created > DateTime.Today);

            Assert.IsNotNull(savedLog, "The upload log cannot be found.");
            Assert.IsTrue(savedLog.Processed, "The value of processes is different than the expected one.");
            Assert.AreEqual(log.FileId, savedLog.FileId, "The if of the file is different than the expected one.");
        }

        [TestMethod]
        [Owner("Mihai Stanescu")]
        public void UpdateUploadLog()
        {
            var universeRep = Sustainalytics.ClientDW.DataAccessLayer.UniversesRepository.GetUniversesRepository(dbCnx);
            UploadLog log = new UploadLog()
            {
                Created = DateTime.UtcNow,
                FileId = Guid.NewGuid(),
                UserId = Guid.NewGuid(),
                UploadType = UploadType.UniversesUpload,
                Processed = false,
                Id = Guid.NewGuid()
            };

            universeRep.AddUploadLogs(log);
            var savedLog = universeRep.GetUploadLog();
            savedLog.Processed = true;

            universeRep.UpdateUploadLog(savedLog);
            var updatedLog = universeRep.GetUploadLog();

            Assert.IsNotNull(savedLog, "The upload log cannot be found.");
            Assert.IsTrue(savedLog.Processed, "The value of processes is different than the expected one.");
            Assert.AreEqual(log.FileId, savedLog.FileId, "The if of the file is different than the expected one.");
        }

        [TestMethod]
        [Owner("Mihai Stanescu")]
        public void AddUploadErrorLog()
        {
            var universeRep = Sustainalytics.ClientDW.DataAccessLayer.UniversesRepository.GetUniversesRepository(dbCnx);
            Guid fileId = Guid.NewGuid();

            UploadLog upl = new UploadLog()
            {
                FileId = fileId,
                Processed = true,
                Created = DateTime.UtcNow,
                UploadType = UploadType.UniversesUpload,
                UserId = Guid.Empty,
                Id = Guid.NewGuid()
            };
            universeRep.AddUploadLogs(upl);

            UploadErrorLog log = new UploadErrorLog()
            {
                FileId = fileId,
                ErrorMessage = "error1",
                RowNumber = 12,
                Worksheet = "Universes"
            };
            UploadErrorLog log2 = new UploadErrorLog()
            {
                FileId = fileId,
                ErrorMessage = "error2",
                RowNumber = 4,
                Worksheet = "CompaniesInUniverses"
            };

            universeRep.AddUploadErrorLog(log);
            universeRep.AddUploadErrorLog(log2);

            var savedLogs = universeRep.GetUploadErrorLogs();

            Assert.IsNotNull(savedLogs, "The saved logs should not be null.");
            Assert.AreEqual(2, savedLogs.Count, "The number of error logs is different than the expected one.");
        }

        [TestMethod]
        [Owner("Mihai Stanescu")]
        public void GetAllCompanyUniverses()
        {
            var universeRep = Sustainalytics.ClientDW.DataAccessLayer.UniversesRepository.GetUniversesRepository(dbCnx);
            Guid company1 = Guid.NewGuid();
            string ciqid1 = "IQ41203";


            UniverseCompany univ1 = new UniverseCompany() { CompanyId = company1, CIQID = ciqid1, LastUpdated = DateTime.UtcNow, UniverseId = 1 };
            UniverseCompany univ2 = new UniverseCompany() { CompanyId = company1, CIQID = ciqid1, LastUpdated = DateTime.UtcNow, UniverseId = 2 };
            UniverseCompany univ3 = new UniverseCompany() { CompanyId = company1, CIQID = ciqid1, LastUpdated = DateTime.UtcNow, UniverseId = 3 };
            UniverseCompany univ4 = new UniverseCompany() { CompanyId = company1, CIQID = ciqid1, LastUpdated = DateTime.UtcNow.AddDays(-7), UniverseId = 6 };
            UniverseCompany univ5 = new UniverseCompany() { CompanyId = company1, CIQID = ciqid1, LastUpdated = DateTime.UtcNow, UniverseId = 8 };
            UniverseCompany univ6 = new UniverseCompany() { CompanyId = company1, CIQID = ciqid1, LastUpdated = DateTime.UtcNow, UniverseId = 5 };
            UniverseCompany univ7 = new UniverseCompany() { CompanyId = company1, CIQID = ciqid1, LastUpdated = DateTime.UtcNow.AddDays(-7), UniverseId = 7 };
            UniverseCompany univ8 = new UniverseCompany() { CompanyId = company1, CIQID = ciqid1, LastUpdated = DateTime.UtcNow, UniverseId = 4 };
            UniverseCompany univ9 = new UniverseCompany() { CompanyId = company1, CIQID = ciqid1, LastUpdated = DateTime.UtcNow.AddDays(-7), UniverseId = 9 };

            universeRep.AddUniverseCompany(univ1);
            universeRep.AddUniverseCompany(univ2);
            universeRep.AddUniverseCompany(univ3);
            universeRep.AddUniverseCompany(univ4);
            universeRep.AddUniverseCompany(univ5);
            universeRep.AddUniverseCompany(univ6);
            universeRep.AddUniverseCompany(univ7);
            universeRep.AddUniverseCompany(univ8);
            universeRep.AddUniverseCompany(univ9);

            var savedUniverseCompanies = universeRep.GetAllCompanyUniverses(company1, DateTime.Today);

            Assert.AreEqual(9, savedUniverseCompanies.Count, "The number of company universes is different than the expected one.");
        }

        [TestMethod]
        [Owner("Mihai Stanescu")]
        public void DeleteUniversesByAccountId()
        {
            var universeRep = Sustainalytics.ClientDW.DataAccessLayer.UniversesRepository.GetUniversesRepository(dbCnx);
            Guid accountId1 = Guid.NewGuid();
            Guid accountId2 = Guid.NewGuid();
            int universeId1 = 1;
            int universeId2 = 2;
            int universeId3 = 3;

            Universe expectedUniverse1 = new Universe() { Id = universeId1, AccountId = accountId1, Created = DateTime.UtcNow, LastUpdated = DateTime.UtcNow, Name = "SomeUniverseName1" };
            Universe expectedUniverse2 = new Universe() { Id = universeId2, AccountId = accountId1, Created = DateTime.UtcNow, LastUpdated = DateTime.UtcNow, Name = "SomeUniverseName2" };
            Universe expectedUniverse3 = new Universe() { Id = universeId3, AccountId = accountId2, Created = DateTime.UtcNow, LastUpdated = DateTime.UtcNow, Name = "SomeUniverseName3" };

            universeRep.AddUniverse(expectedUniverse1);
            universeRep.AddUniverse(expectedUniverse2);
            universeRep.AddUniverse(expectedUniverse3);

            
            UniverseCompany univ1 = new UniverseCompany() { CompanyId = Guid.NewGuid(), CIQID = "IQ412031", LastUpdated = DateTime.UtcNow, UniverseId = universeId1 };
            UniverseCompany univ2 = new UniverseCompany() { CompanyId = Guid.NewGuid(), CIQID = "IQ412032", LastUpdated = DateTime.UtcNow, UniverseId = universeId1 };
            UniverseCompany univ3 = new UniverseCompany() { CompanyId = Guid.NewGuid(), CIQID = "IQ412033", LastUpdated = DateTime.UtcNow, UniverseId = universeId1 };
            UniverseCompany univ4 = new UniverseCompany() { CompanyId = Guid.NewGuid(), CIQID = "IQ412034", LastUpdated = DateTime.UtcNow, UniverseId = universeId1 };
            UniverseCompany univ5 = new UniverseCompany() { CompanyId = Guid.NewGuid(), CIQID = "IQ412035", LastUpdated = DateTime.UtcNow, UniverseId = universeId1 };
            UniverseCompany univ6 = new UniverseCompany() { CompanyId = Guid.NewGuid(), CIQID = "IQ412036", LastUpdated = DateTime.UtcNow, UniverseId = universeId2 };
            UniverseCompany univ7 = new UniverseCompany() { CompanyId = Guid.NewGuid(), CIQID = "IQ412037", LastUpdated = DateTime.UtcNow, UniverseId = universeId2 };
            UniverseCompany univ8 = new UniverseCompany() { CompanyId = Guid.NewGuid(), CIQID = "IQ412038", LastUpdated = DateTime.UtcNow, UniverseId = universeId3 };
            UniverseCompany univ9 = new UniverseCompany() { CompanyId = Guid.NewGuid(), CIQID = "IQ412039", LastUpdated = DateTime.UtcNow, UniverseId = universeId3 };

            universeRep.AddUniverseCompany(univ1);
            universeRep.AddUniverseCompany(univ2);
            universeRep.AddUniverseCompany(univ3);
            universeRep.AddUniverseCompany(univ4);
            universeRep.AddUniverseCompany(univ5);
            universeRep.AddUniverseCompany(univ6);
            universeRep.AddUniverseCompany(univ7);
            universeRep.AddUniverseCompany(univ8);
            universeRep.AddUniverseCompany(univ9);

            universeRep.DeleteUniversesByAccountId(accountId1);

            var savedUniverseCompanies1 = universeRep.GetUniverseCompanies(universeId1);
            var savedUniverseCompanies3 = universeRep.GetUniverseCompanies(universeId3);
            var universes1 = universeRep.GetUniversesByAccountId(accountId1);
            var universes2 = universeRep.GetUniversesByAccountId(accountId2);

            Assert.IsFalse(universes1.Any(), "The universes for account1 should be null.");
            Assert.IsTrue(universes2.Any(), "The universes for account2 should not be null");
            Assert.IsFalse(savedUniverseCompanies1.Any(), "The number of companies for universe 1 should be 0.");
            Assert.IsTrue(savedUniverseCompanies3.Any(), "There should be at least 1 company for universe 2.");
            Assert.IsFalse(savedUniverseCompanies3.Any(uc => uc.UniverseId == universeId1), "The universe companies return belong to a different universe.");

        }

        [TestMethod]
        [Owner("Mihai Stanescu")]
        public void DeleteFeatureUniversesByAccountId()
        {
            var universeRep = Sustainalytics.ClientDW.DataAccessLayer.UniversesRepository.GetUniversesRepository(dbCnx);
            Guid accountId1 = Guid.NewGuid();
            Guid accountId2 = Guid.NewGuid();

            FeatureUniverse featureUniverse1 = new FeatureUniverse() { AccountId = accountId1, Feature = AccountFeature.GlobalCompactNonCompliant, UniverseIds = new List<int>() { 1, 5, 7 } };
            FeatureUniverse featureUniverse2 = new FeatureUniverse() { AccountId = accountId1, Feature = AccountFeature.GlobalCompactWatchlist, UniverseIds = new List<int>() { 1, 5, 7 } };
            FeatureUniverse featureUniverse3 = new FeatureUniverse() { AccountId = accountId2, Feature = AccountFeature.GlobalCompactNonCompliant, UniverseIds = new List<int>() { 1, 5, 7 } };
            FeatureUniverse featureUniverse4 = new FeatureUniverse() { AccountId = accountId2, Feature = AccountFeature.GlobalCompactWatchlist, UniverseIds = new List<int>() { 1, 5, 7 } };

            universeRep.AddFeatureUniverse(featureUniverse1);
            universeRep.AddFeatureUniverse(featureUniverse2);
            universeRep.AddFeatureUniverse(featureUniverse3);
            universeRep.AddFeatureUniverse(featureUniverse4);

            universeRep.DeleteFeatureUniversesByAccountId(accountId1);

            var universe1 = universeRep.GetFeatureUniveseByAccountId(accountId1);
            var universe2 = universeRep.GetFeatureUniveseByAccountId(accountId2);

            Assert.IsFalse(universe1.Any(), "The universes for account1 should be null.");
            Assert.IsTrue(universe2.Any(), "The universes for account 2 should not be null.");
        }

        [TestMethod]
        [Owner("Mihai Stanescu")]
        public void DeleteUniversesByAccountId_NonExistentAccount()
        {
            var universeRep = Sustainalytics.ClientDW.DataAccessLayer.UniversesRepository.GetUniversesRepository(dbCnx);
            Guid accountId = Guid.NewGuid();

            universeRep.DeleteUniversesByAccountId(accountId);

            var universes = universeRep.GetUniversesByAccountId(accountId);
            Assert.IsFalse(universes.Any(), "The universes list should be empty.");
        }

        [TestMethod]
        [Owner("Mihai Stanescu")]
        public void DeleteFeatureUniversesByAccountId_NonExistentAccount()
        {
            var universeRep = Sustainalytics.ClientDW.DataAccessLayer.UniversesRepository.GetUniversesRepository(dbCnx);
            Guid accountId = Guid.NewGuid();

            universeRep.DeleteFeatureUniversesByAccountId(accountId);

            var fUniverses = universeRep.GetFeatureUniveseByAccountId(accountId);
            Assert.IsFalse(fUniverses.Any(), "The universes list should be empty.");
        }
    }
}
